﻿using Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class goodsDal
    {
        /// <summary>
        /// 通过商品分类查商家商品
        /// </summary>
        /// <param name="classid"></param>
        /// <param name="merchant_id"></param>
        /// <returns></returns>
        public static List<ms_goods> Querybyclassid(string classid, string merchant_id)
        {
            string sql = "select * from ms_goods where classify_id=@classify_id and merchant_id=@merchant_id  and status=1";
            MySqlParameter[] values = new MySqlParameter[2];
            values[0] = new MySqlParameter("classify_id", classid);
            values[1] = new MySqlParameter("merchant_id", merchant_id);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            List<ms_goods> gdlist = new List<ms_goods>();
            while (rd.Read())
            {
                ms_goods goods = new ms_goods();
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.price = rd["price"].ToString();
                goods.sale = rd["sale"].ToString();
                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();
                goods.specs = rd["specs"].ToString();
              
                goods.weight = rd["weight"].ToString();
                
                goods.status = rd["status"].ToString();
                gdlist.Add(goods);
            }
            rd.Close();
            return gdlist;
        }
        /// <summary>
        /// 查商户所有上架商品
        /// </summary>
        /// <param name="merchant_id"></param>
        /// <returns></returns>
        public static List<ms_goods> Queryallbymid(string merchant_id)
        {
            string sql = "select * from ms_goods where merchant_id=@merchant_id  and status=1";
            MySqlParameter[] values = new MySqlParameter[1];
            values[0] = new MySqlParameter("merchant_id", merchant_id);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            List<ms_goods> gdlist = new List<ms_goods>();
            while (rd.Read())
            {
                ms_goods goods = new ms_goods();
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.price = rd["price"].ToString();
                goods.sale = rd["sale"].ToString();
                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();
                goods.specs = rd["specs"].ToString();

                goods.weight = rd["weight"].ToString();

                goods.status = rd["status"].ToString();
                gdlist.Add(goods);
            }
            rd.Close();
            return gdlist;
        }
        /// <summary>
        /// 通过商户查上架/下架商品
        /// </summary>
        /// <param name="merchant_id"></param>
        /// <returns></returns>
        public static List<ms_goods> Querybymerchantid(string merchant_id, string status)
        {
            string sql = "select * from ms_goods INNER join ms_classify on ms_goods.classify_id=ms_classify.id where  ms_goods.merchant_id=@merchant_id and ms_goods.status=@status";
            MySqlParameter[] values = new MySqlParameter[2];
            values[0] = new MySqlParameter("merchant_id", merchant_id);
            values[1] = new MySqlParameter("status", status);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            List<ms_goods> gdlist = new List<ms_goods>();
            while (rd.Read())
            {
                ms_goods goods = new ms_goods();
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.classify_name = rd["title"].ToString();
                goods.price = rd["price"].ToString();
               
                goods.sale = rd["sale"].ToString();
              
                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();
              
                goods.specs = rd["specs"].ToString();
                
               
                goods.weight = rd["weight"].ToString();
              
                goods.status = rd["status"].ToString();
                gdlist.Add(goods);
            }
            rd.Close();
            return gdlist;
        }
        /// <summary>
        /// 通过商品名称模糊查询
        /// </summary>
        /// <param name="merchant_id"></param>
        /// <param name="status"></param>
        /// <param name="goodname"></param>
        /// <returns></returns>
        public static List<ms_goods> Querybygoodname(string merchant_id, string status,string goodname)
        {
            string sql = "select * from ms_goods INNER join ms_classify on ms_goods.classify_id=ms_classify.id where  ms_goods.merchant_id=@merchant_id and ms_goods.status=@status and goods_name like @goods_name";
            MySqlParameter[] values = new MySqlParameter[3];
            values[0] = new MySqlParameter("merchant_id", merchant_id);
            values[1] = new MySqlParameter("status", status);
            values[2] = new MySqlParameter("goods_name", "%"+goodname+"%");
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            List<ms_goods> gdlist = new List<ms_goods>();
            while (rd.Read())
            {
                ms_goods goods = new ms_goods();
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.classify_name = rd["title"].ToString();
                goods.price = rd["price"].ToString();

                goods.sale = rd["sale"].ToString();

                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();

                goods.specs = rd["specs"].ToString();


                goods.weight = rd["weight"].ToString();

                goods.status = rd["status"].ToString();
                gdlist.Add(goods);
            }
            rd.Close();
            return gdlist;
        }
        /// <summary>
        /// 通过商品ID查商品详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ms_goods Querybyid(string id)
        {
            string sql = "select * from ms_goods where id=@id";
            MySqlParameter[] values = new MySqlParameter[1];
            values[0] = new MySqlParameter("id", id);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            ms_goods goods = new ms_goods();
            if (rd.Read())
            {
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.price = rd["price"].ToString();
                goods.sale = rd["sale"].ToString();
                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();
                goods.specs = rd["specs"].ToString();
                goods.weight = rd["weight"].ToString();
               
                goods.status = rd["status"].ToString();
               
            }
            rd.Close();
            return goods;
        }
        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="status"></param>
        /// <param name="goodid"></param>
        /// <returns></returns>
        public static int Updatestatus(string status, string goodid)
        {
            string sql = "update ms_goods set status=@status where id=@id";
            MySqlParameter[] values = new MySqlParameter[2];
            values[0] = new MySqlParameter("id", goodid);
            values[1] = new MySqlParameter("status", status);
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="goodid"></param>
        /// <returns></returns>
        public static int Delgoods(string goodid)
        {
            string sql = "delete from  ms_goods  where id=@id";
            MySqlParameter[] values = new MySqlParameter[1];
            values[0] = new MySqlParameter("id", goodid);
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 修改分类ID
        /// </summary>
        /// <param name="classid"></param>
        /// <param name="goodid"></param>
        /// <returns></returns>
        public static int Updateclass(string classid, string goodid)
        {
            string sql = "update ms_goods set classify_id=@classify_id where id=@id";
            MySqlParameter[] values = new MySqlParameter[2];
            values[0] = new MySqlParameter("id", goodid);
            values[1] = new MySqlParameter("classify_id", classid);
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 添加商品
        /// </summary>
        /// <param name="good"></param>
        /// <returns></returns>
        public static int Addgoods(ms_goods good)
        {
            string sql = "insert into ms_goods(merchant_id,goods_name,classify_id,cover,carousel,market_price,price,describes,specs,status,add_time) values(@merchant_id,@goods_name,@classify_id,@cover,@carousel,@market_price,@price,@describes,@specs,@status,@add_time)";
            MySqlParameter[] values = new MySqlParameter[11];
            values[0] = new MySqlParameter("merchant_id", good.merchant_id);
            values[1] = new MySqlParameter("goods_name", good.goods_name);
            values[2] = new MySqlParameter("classify_id", good.classify_id);
            values[3] = new MySqlParameter("cover", good.cover);
            values[4] = new MySqlParameter("carousel", good.carousel);
            values[5] = new MySqlParameter("market_price", good.market_price);
            values[6] = new MySqlParameter("price", good.price);           
            values[7] = new MySqlParameter("describes", good.describes);
            values[8] = new MySqlParameter("specs", good.specs);
            values[9] = new MySqlParameter("status", good.status);
            values[10] = new MySqlParameter("add_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 添加商品
        /// </summary>
        /// <param name="good"></param>
        /// <returns></returns>
        public static int Addgoods1(ms_goods good)
        {
            string sql = "insert into ms_goods(merchant_id,goods_name,classify_id,cover,carousel,market_price,price,describes,specs,status,add_time,bar_code,brand,guarantee,weight) values(@merchant_id,@goods_name,@classify_id,@cover,@carousel,@market_price,@price,@describes,@specs,@status,@add_time,@bar_code,@brand,@guarantee,@weight)";
            MySqlParameter[] values = new MySqlParameter[15];
            values[0] = new MySqlParameter("merchant_id", good.merchant_id);
            values[1] = new MySqlParameter("goods_name", good.goods_name);
            values[2] = new MySqlParameter("classify_id", good.classify_id);
            values[3] = new MySqlParameter("cover", good.cover);
            values[4] = new MySqlParameter("carousel", good.carousel);
            values[5] = new MySqlParameter("market_price", good.market_price);
            values[6] = new MySqlParameter("price", good.price);
            values[7] = new MySqlParameter("describes", good.describes);
            values[8] = new MySqlParameter("specs", good.specs);
            values[9] = new MySqlParameter("status", good.status);
            values[10] = new MySqlParameter("add_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            values[11] = new MySqlParameter("bar_code", good.bar_code);
            values[12] = new MySqlParameter("brand", good.brand);
            values[13] = new MySqlParameter("guarantee", good.guarantee);
            values[14] = new MySqlParameter("weight", good.weight);
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 更新商品
        /// </summary>
        /// <param name="good"></param>
        /// <returns></returns>
        public static int Updategoods(ms_goods good, string goodsid)
        {
            string sql = "update ms_goods set goods_name=@goods_name,classify_id=@classify_id,cover=@cover,carousel=@carousel,market_price=@market_price,price=@price,describes=@describes,specs=@specs,status=@status where id=@id";
            MySqlParameter[] values = new MySqlParameter[10];
            values[0] = new MySqlParameter("goods_name", good.goods_name);
            values[1] = new MySqlParameter("classify_id", good.classify_id);
            values[2] = new MySqlParameter("cover", good.cover);
            values[3] = new MySqlParameter("carousel", good.carousel);
            values[4] = new MySqlParameter("market_price", good.market_price);
            values[5] = new MySqlParameter("price", good.price);           
            values[6] = new MySqlParameter("describes", good.describes);
            if (!string.IsNullOrEmpty(good.specs))
                values[7] = new MySqlParameter("specs", good.specs);
            else
                values[7] = new MySqlParameter("specs", null);
            values[8] = new MySqlParameter("status", good.status);
            values[9] = new MySqlParameter("id", goodsid);
            return DBHelper.ExcutUpdate(sql, values);
        }
        /// <summary>
        /// 查该商品名称是否存在
        /// </summary>
        /// <param name="goodname"></param>
        /// <param name="merchantid"></param>
        /// <returns></returns>
        public static int Querybyname(string goodname, string merchantid)
        {
            string sql = "select count(*) as count from ms_goods where goods_name=@goods_name and merchant_id=@merchant_id";
            int count = 0;
            MySqlParameter[] values = new MySqlParameter[2];
            values[0] = new MySqlParameter("goods_name", goodname);
            values[1] = new MySqlParameter("merchant_id", merchantid);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            if (rd.Read())
            {
                if (string.IsNullOrEmpty(rd["count"].ToString()))
                    count = Convert.ToInt32(rd["count"]);
            }
            rd.Close();
            return count;
        }
        /// <summary>
        /// 通过商品分类查商家商品
        /// </summary>
        /// <param name="classid"></param>
        /// <param name="merchant_id"></param>
        /// <returns></returns>
        public static List<ms_goods> Querybyclassid(string classid, string merchant_id, string u_sage)
        {
            string sql = "select * from ms_goods where classify_id=@classify_id and merchant_id=@merchant_id and status=1  and u_sage=@u_sage";
            MySqlParameter[] values = new MySqlParameter[3];
            values[0] = new MySqlParameter("classify_id", classid);
            values[1] = new MySqlParameter("merchant_id", merchant_id);
            values[2] = new MySqlParameter("u_sage", u_sage);
            MySqlDataReader rd = DBHelper.ExcutQuery(sql, values);
            List<ms_goods> gdlist = new List<ms_goods>();
            while (rd.Read())
            {
                ms_goods goods = new ms_goods();
                goods.id = rd["id"].ToString();
                goods.bar_code = rd["bar_code"].ToString();
                goods.merchant_id = rd["merchant_id"].ToString();
                goods.goods_name = rd["goods_name"].ToString();
                goods.classify_id = rd["classify_id"].ToString();
                goods.cover = rd["cover"].ToString();
                goods.carousel = rd["carousel"].ToString();
                goods.market_price = rd["market_price"].ToString();
                goods.price = rd["price"].ToString();
              
                goods.sale = rd["sale"].ToString();
               
                goods.describes = rd["describes"].ToString();
                goods.introduce = rd["introduce"].ToString();
               
                goods.specs = rd["specs"].ToString();
                
               
                goods.weight = rd["weight"].ToString();
               

                gdlist.Add(goods);
            }
            rd.Close();
            return gdlist;
        }
    }
}
